/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package CONTROL;

import UTILITY.KetNoiDuLieu;
import VIEW.frmSinhVien;
import java.sql.Statement;
import MODEL.sinhvien;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.swing.JOptionPane;

/**
 *
 * @author CONGTHANH
 */
public class SinhVien_Control {

  //  KetNoiDuLieu kn = new KetNoiDuLieu();
   // Statement st = kn.getStatement();
    String sql;
    frmSinhVien f = new frmSinhVien();
    private static ArrayList<sinhvien> dssv = new ArrayList<sinhvien>();

    public SinhVien_Control() throws SQLException {
        if (dssv.size() <= 0) {
            Load_DS_SV();
        }

    }
    
   public boolean Tim(String ma) {
      
         // ArrayList<sinhvien> kq=SinhVien_Control.layTATCASV();
        for (sinhvien sv : f.getDSSV()) {
            
            if (sv.getMasv() == ma){
                return true;
            }
        }
        return false;
    }
    
public void Them(sinhvien sv) throws SQLException {
        try {
            if(Tim(sv.getMasv()))
            {
                JOptionPane.showMessageDialog(null, "Trung ma");
            }
            else
            {
                f.getDSSV().add(sv);//add vao arraylist
                KetNoiDuLieu kn = new KetNoiDuLieu();
                Statement st = kn.getStatement();
                String sql = "insert into sinhvien(masv,tensv,namsinh,diachi,dienthoai,email,makhoa) values('" + sv.getMasv() + "','" + sv.getTensv() + "','" + sv.getNgaysinh() + "','" + sv.getDiachi() + "','" + sv.getDienthoai() + "','" + sv.getEmail() + "','" + sv.getMakhoa() + "')";
                st.executeUpdate(sql);//add vao csdl
                dssv.add(sv);

                JOptionPane.showMessageDialog(null, "Them thanh cong");
            }
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Them that bai");
          //  JOptionPane.showMessageDialog(null, e);
        }
    }

    public ArrayList<sinhvien> getDSSV() {
        return dssv;
    }

   

    public void Load_DS_SV(){
     
        try{
            KetNoiDuLieu kn=new KetNoiDuLieu();
        String qry = "SELECT * FROM SINHVIEN";
        ResultSet rs =kn.executeQuery(qry);
        while (rs.next()) {
            sinhvien sv = new sinhvien();
            sv.setMasv(rs.getString("MASV"));
            sv.setTensv(rs.getString("TENSV"));
            sv.setNgaysinh(rs.getString("NAMSINH"));
            sv.setDiachi(rs.getString("DIACHI"));
            sv.setDienthoai(rs.getString("DIENTHOAI"));
            sv.setEmail(rs.getString("EMAIL"));
            sv.setMakhoa(rs.getString("MAKHOA"));
            dssv.add(sv);
        
        }
        }catch(Exception ex){ System.out.println(ex.getMessage());}
        

    }
     public static ArrayList<sinhvien> layTATCASV(){
        ArrayList<sinhvien> kq=new ArrayList<sinhvien>();
        try{
            String sql="SELECT * FROM SINHVIEN ";
            KetNoiDuLieu kn=new KetNoiDuLieu();
            kn.open();
            ResultSet rs=kn.executeQuery(sql);
            while(rs.next()){
                sinhvien kh=new sinhvien();
                kh.setMasv(rs.getString(1));
                kh.setTensv(rs.getString(2));
                kh.setNgaysinh(rs.getString(3));
                kh.setDiachi(rs.getString(4));
                kh.setDienthoai(rs.getString(5));
                kh.setEmail(rs.getString(6));
                kh.setMakhoa(rs.getString(7));
                
                kq.add(kh);
                
            }
            kn.close();
            
            
        }catch(Exception ex){
            System.out.println(ex.getMessage());
        }
        return kq;
    }


    public static int xoatheoma(sinhvien sv){
        int kq=-1;
        try{
            KetNoiDuLieu kn=new KetNoiDuLieu();
            kn.open();
            String sql="delete from sinhvien where Masv='" +sv.getMasv()+ "'";
            kq=kn.executeUpdate(sql);
            kn.close();
        }catch(Exception ex){
            System.out.println(ex.getMessage());
        }
        return kq;
    }

    public void Xoa(sinhvien sv) throws SQLException {
        
            KetNoiDuLieu kn = new KetNoiDuLieu();
    Statement st = kn.getStatement();
        f.getDSSV().remove(sv);//remove khoi arraylit

        String sql = "delete from sinhvien where masv='" + sv.getMasv() + "'";
        st.executeUpdate(sql);
    }
    public void Xoasinhvien(int Pos,sinhvien sv) throws SQLException
    {
            KetNoiDuLieu kn = new KetNoiDuLieu();
    Statement st = kn.getStatement();
        String qry = "DELETE FROM sinhvien WHERE MAsv = '" + sv.getMasv()+"'";
        st.executeUpdate(qry);
        dssv.remove(Pos);
        
    } 
    public int docsinhvien(sinhvien sv ) throws SQLException {
            KetNoiDuLieu kn = new KetNoiDuLieu();
    Statement st = kn.getStatement();
            
            String queryString = "SELECT * FROM NHANVIEN";
            ResultSet rs = st.executeQuery(queryString);
            
           int cout=0;
            while(rs.next()) {
                cout++;
            }
            if(cout ==1)
                return 1;
            else
              return 0;  
    }
    public static int layma(){
        int kq=0;
        try{
            String sql="select masv from sinhvien order by Masv desc limit 0,1 ";
            KetNoiDuLieu kn=new KetNoiDuLieu();
            kn.open();
            ResultSet rs=kn.executeQuery(sql);
            while(rs.next()){
                kq=Integer.parseInt(rs.getString(1));
                
            }
            kn.close();
            
            
        }catch(Exception ex){
            System.out.println(ex.getMessage());
        }
        return kq+1;
    }
     public static ArrayList<sinhvien> timkiemtheoten(sinhvien ten){
        ArrayList<sinhvien> kq=new ArrayList<sinhvien>();
        try{
             String sql="select * from khachhang where HoTenKH like '"+ten+"%'";
            KetNoiDuLieu kn=new KetNoiDuLieu();
            kn.open();
            ResultSet rs=kn.executeQuery(sql);
            while(rs.next()){
                sinhvien sv=new sinhvien();
                sv.setMakhoa(rs.getString(1));
                sv.setTensv(rs.getString(2));
                sv.setNgaysinh(rs.getString(3));
                sv.setDiachi(rs.getString(4));
                sv.setDienthoai(rs.getString(5));
                 sv.setEmail(rs.getString(4));
                sv.setMakhoa(rs.getString(5));
                
                kq.add(sv);
                
            }
            kn.close();
            
            
        }catch(Exception ex){
            System.out.println(ex.getMessage());
        }
        return kq;
    }
      public static int suasv(sinhvien sv){
        int kq=-1;
        try{
            
           String sql="update sinhvien set tensv='"+sv.getTensv()+"',namsinh='"+sv.getNgaysinh()+"',DiaChi='"+sv.getDiachi()+"',Dienthoai="+sv.getDienthoai() +",email='"+sv.getEmail()+"',Makhoa='"+sv.getMakhoa()+"' where Masv='"+sv.getMasv()+"'";
            KetNoiDuLieu hp=new KetNoiDuLieu();
              Statement st=hp.getStatement();
            hp.open();
            kq=st.executeUpdate(sql);
            hp.close();
            
        }catch(Exception ex){
            System.out.println(ex.getMessage());
        }
        return kq;
    }
      public String tutangma()
      {
        String   kq="SV";
          String sql="select Masv from sinhvien order by Masv desc limit 0,1 ";
          try {
			//PreparedStatement stm = getConnection().prepareStatement(sql);
                        KetNoiDuLieu kn=new KetNoiDuLieu();
                        Statement st=kn.getStatement();
			ResultSet rs = st.executeQuery(sql);
                        
			if ((rs != null) && rs.next())
                        {
                            Integer stt=Integer.parseInt(rs.getString(1).toString().substring(2));
                            stt++;
                            String tam=stt.toString();
                            for(int i=0;i<(3-tam.length());i++){
                                kq+="0";
                            }
                            kq+=stt.toString();
                        }
			
			//stm.close();
                        //closeConnection();
          }catch(Exception ex){}
         
         return kq;
     }
    
}
